package com.neusoft.elm.util;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;

/**
 * 数据库初始化工具
 */
public class DatabaseInitializer {
    
    public static void main(String[] args) {
        System.out.println("=== 开始初始化数据库 ===");
        
        try {
            // 创建数据库和表
            createTables();
            
            // 插入测试数据
            insertTestData();
            
            System.out.println("✓ 数据库初始化完成");
            
        } catch (Exception e) {
            System.out.println("✗ 数据库初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * 创建数据库表
     */
    private static void createTables() throws Exception {
        System.out.println("创建数据库表...");
        
        Connection conn = null;
        Statement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            
            // 创建用户表
            String userTableSql = "CREATE TABLE IF NOT EXISTS `user` (" +
                "  `userId` varchar(20) NOT NULL COMMENT '用户ID'," +
                "  `password` varchar(20) NOT NULL COMMENT '密码'," +
                "  `userName` varchar(20) DEFAULT NULL COMMENT '用户名'," +
                "  `userSex` int(11) DEFAULT NULL COMMENT '性别：0女，1男'," +
                "  `userImg` varchar(255) DEFAULT NULL COMMENT '头像'," +
                "  PRIMARY KEY (`userId`)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'";
            
            stmt.executeUpdate(userTableSql);
            System.out.println("✓ 用户表创建成功");
            
            // 创建商家表
            String businessTableSql = "CREATE TABLE IF NOT EXISTS `business` (" +
                "  `businessId` int(11) NOT NULL AUTO_INCREMENT COMMENT '商家ID'," +
                "  `password` varchar(20) NOT NULL COMMENT '密码'," +
                "  `businessName` varchar(50) NOT NULL COMMENT '商家名称'," +
                "  `businessAddress` varchar(255) NOT NULL COMMENT '商家地址'," +
                "  `businessExplain` varchar(255) DEFAULT NULL COMMENT '商家介绍'," +
                "  `starPrice` double(5,2) DEFAULT NULL COMMENT '起送费'," +
                "  `deliveryPrice` double(5,2) DEFAULT NULL COMMENT '配送费'," +
                "  `distance` double(5,2) DEFAULT 0.00 COMMENT '距离（公里）'," +
                "  `monthlySales` int(11) DEFAULT 0 COMMENT '月销量'," +
                "  `rating` double(3,1) DEFAULT 5.0 COMMENT '评分'," +
                "  PRIMARY KEY (`businessId`)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商家表'";
            
            stmt.executeUpdate(businessTableSql);
            System.out.println("✓ 商家表创建成功");
            
            // 创建食品表
            String foodTableSql = "CREATE TABLE IF NOT EXISTS `food` (" +
                "  `foodId` int(11) NOT NULL AUTO_INCREMENT COMMENT '食品ID'," +
                "  `foodName` varchar(50) NOT NULL COMMENT '食品名称'," +
                "  `foodExplain` varchar(255) DEFAULT NULL COMMENT '食品介绍'," +
                "  `foodImg` varchar(255) DEFAULT NULL COMMENT '食品图片'," +
                "  `foodPrice` double(5,2) NOT NULL COMMENT '食品价格'," +
                "  `businessId` int(11) NOT NULL COMMENT '所属商家ID'," +
                "  PRIMARY KEY (`foodId`)," +
                "  KEY `fk_food_business` (`businessId`)," +
                "  CONSTRAINT `fk_food_business` FOREIGN KEY (`businessId`) REFERENCES `business` (`businessId`)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='食品表'";
            
            stmt.executeUpdate(foodTableSql);
            System.out.println("✓ 食品表创建成功");
            
        } finally {
            if (stmt != null) stmt.close();
            DBUtil.close();
        }
    }
    
    /**
     * 插入测试数据
     */
    private static void insertTestData() throws Exception {
        System.out.println("插入测试数据...");
        
        Connection conn = null;
        Statement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            
            // 插入商家数据
            String[] businessData = {
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (1, '123456', '麦当劳(中山路店)', '北京市朝阳区中山路88号', '全球知名快餐连锁品牌，提供汉堡、薯条、饮料等美食', 20.00, 5.00, 1.2, 2580, 4.8)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (2, '123456', '肯德基(王府井店)', '北京市东城区王府井大街168号', 'KFC肯德基，美味炸鸡专家，新鲜现做', 25.00, 6.00, 2.1, 3200, 4.7)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (3, '123456', '海底捞火锅', '北京市海淀区中关村大街1号', '知名火锅品牌，服务贴心，食材新鲜', 50.00, 8.00, 3.5, 1850, 4.9)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (4, '123456', '星巴克咖啡', '北京市朝阳区建国门外大街2号', '全球知名咖啡连锁品牌，提供优质咖啡和轻食', 30.00, 7.00, 1.8, 1200, 4.6)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (5, '123456', '兰州拉面馆', '北京市西城区西单北大街66号', '正宗兰州牛肉拉面，汤清面白，味道鲜美', 15.00, 4.00, 0.8, 980, 4.5)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (6, '123456', '川味小厨', '北京市丰台区丰台路128号', '正宗川菜，麻辣鲜香，口味地道', 25.00, 5.00, 2.3, 1560, 4.4)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (7, '123456', '日式料理店', '北京市朝阳区三里屯路19号', '精致日式料理，新鲜刺身，寿司专家', 40.00, 10.00, 4.2, 750, 4.7)",
                "INSERT IGNORE INTO `business` (`businessId`, `password`, `businessName`, `businessAddress`, `businessExplain`, `starPrice`, `deliveryPrice`, `distance`, `monthlySales`, `rating`) VALUES (8, '123456', '黄焖鸡米饭', '北京市昌平区回龙观东大街88号', '经典黄焖鸡米饭，营养丰富，价格实惠', 12.00, 3.00, 1.5, 2100, 4.3)"
            };
            
            for (String sql : businessData) {
                stmt.executeUpdate(sql);
            }
            
            System.out.println("✓ 商家测试数据插入成功");
            
        } finally {
            if (stmt != null) stmt.close();
            DBUtil.close();
        }
    }
}